package flights.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import flights.bi.User;

public class UserHandler {

	public static User getUserByCredentials(String email, String password) throws SQLException
	{
		User user = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM users WHERE email_address=? and password=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setString(1,email);
			st.setString(2,password);
			ResultSet rs = st.executeQuery();
			user = extractUsersFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return user;
	}
	
	
	
	private static User extractUsersFromRS(ResultSet ResultSet) throws SQLException {
		User user = null;
		int nRowCount = 0;
		while (ResultSet.next()) {
			int user_id = ResultSet.getInt("user_id");
			String user_name = ResultSet.getString("user_name");
			String password = ResultSet.getString("password");
			Date   registration_date = ResultSet.getDate("registration_date");
			String first_name = ResultSet.getString("first_name");
			String last_name = ResultSet.getString("last_name");
			String address = ResultSet.getString("address");
			String email_address = ResultSet.getString("email_address");

			user = new User(user_id, user_name, password, registration_date, first_name, last_name, address, email_address);
			nRowCount++;
		}
		
		if (nRowCount != 1)
		{
			return null;
		}
		
		return user;
	}



	public static int deleteTicketBySeat(int nFlight_SeatID) throws SQLException 
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM ticket where seat_id = ?";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlight_SeatID);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;		
	}



	public static User getUserByID(int parseInt) throws SQLException {
		User user = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM users WHERE user_id = ?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,parseInt);
			ResultSet rs = st.executeQuery();
			user = extractUsersFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return user;
	}
	
}
